﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace CommonUtil
{
    public class DataTableHelper
    {
        private class FieldInfo
        {
            public string RelationName;  //关系名称
            public string ColumnName;  //列名
            public string ColumnAlias;    //别名
            public string ColumnAggre;  //聚合方式：sum/avg/count等
        }

        private ArrayList m_FieldInfoList;
        private string m_FieldString;
        private ArrayList GroupByFieldInfoList;
        private string GroupByFieldString;
        //
        private DataTable GroupDataTable;
        private DataColumn GroupDataColumn;
        //

        #region 获取新生成的DataTable并返回
        /// <summary>
        /// 公共方法：返回新DataTable
        /// </summary>
        /// <param name="tableName">新DT名</param>
        /// <param name="sourceTable">源DataTable</param>
        /// <param name="fieldList">条件</param>
        /// <param name="rowFilter">过滤</param>
        /// <param name="groupBy">分组</param>
        /// <returns></returns>
        public DataTable SelectGroupDataTable(string tableName, DataTable sourceTable, string fieldList, string rowFilter, string groupBy)
        {
            DataTable dt = CreateGroupDataTable(tableName, sourceTable, fieldList);
            InsertGroupDataTable(dt, sourceTable, fieldList, rowFilter, groupBy);
            return dt;
        }
        #endregion 获取新生成的DataTable并返回

        #region 生成新的DataTable和表结构
        /// <summary>
        /// 根据源DataTable和条件生成新的DataTable
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="SourceDt"></param>
        /// <param name="FieldString"></param>
        /// <returns></returns>
        private DataTable CreateGroupDataTable(string tableName, DataTable SourceDt, string FieldString)
        {
            if (String.IsNullOrEmpty(FieldString) == true)
            {
                GroupDataTable = SourceDt.Clone();
            }
            else
            {
                ParseGroupByFieldString(FieldString);
                GroupDataTable = new DataTable(tableName);
                SetDataColumn(GroupByFieldInfoList, SourceDt);
            }
            return GroupDataTable;
        }
        #endregion 生成新的DataTable和表结构

        #region 插入记录到新的DataTable
        /// <summary>
        /// 将符合条件的记录插入到新的DataTable
        /// </summary>
        /// <param name="destTable"></param>
        /// <param name="sourceTable"></param>
        /// <param name="fieldList"></param>
        /// <param name="rowFilter"></param>
        /// <param name="groupBy"></param>
        private void InsertGroupDataTable(DataTable destTable, DataTable sourceTable, string fieldList,
                                       string rowFilter, string groupBy)
        {
            if (fieldList == null || fieldList.Length == 0)
            {
                return;
            }
            ParseGroupByFieldString(fieldList);
            ParseFieldList(groupBy, false);
            DataRow[] rows = null;
            if (string.IsNullOrEmpty(groupBy))
            {
                rows = sourceTable.Select(rowFilter);
            }
            else
            {
                rows = sourceTable.Select(rowFilter, groupBy);
            }
            DataRow lastSourceRow = null, destRow = null;
            bool sameRow;
            int rowCount = 0;
            foreach (DataRow sourceRow in rows)
            {
                sameRow = false;
                if (lastSourceRow != null)
                {
                    sameRow = true;
                    if (m_FieldInfoList != null)
                    {
                        foreach (FieldInfo Field in m_FieldInfoList)
                        {
                            if (!ColumnEqual(lastSourceRow[Field.ColumnName], sourceRow[Field.ColumnName]))
                            {
                                sameRow = false;
                                break;
                            }
                        }
                    }
                    if (!sameRow)
                    {
                        destTable.Rows.Add(destRow);
                    }
                }
                if (!sameRow)
                {
                    destRow = destTable.NewRow();
                    rowCount = 0;
                }
                rowCount += 1;

                //TODO
                decimal sumAll = 0.000M;
                foreach (FieldInfo field in GroupByFieldInfoList)
                {
                    switch (field.ColumnAggre)
                    {
                        case null:
                        case "":
                        case "last":
                            destRow[field.ColumnAlias] = sourceRow[field.ColumnName];
                            break;
                        case "first":
                            if (rowCount == 1)
                            {
                                destRow[field.ColumnAlias] = sourceRow[field.ColumnName];
                            }
                            break;
                        case "count":
                            destRow[field.ColumnAlias] = rowCount;
                            break;
                        case "sum":
                            destRow[field.ColumnAlias] = Convert.ToDecimal(Add(destRow[field.ColumnAlias], sourceRow[field.ColumnName])).ToString("0.000");
                            sumAll += Convert.ToDecimal(destRow[field.ColumnAlias].ToString());
                            break;
                        case "avg":
                            //if (m_FieldInfoList != null)
                            //{
                            //    destRow[field.ColumnAlias] = Decimal.Parse((Convert.ToDecimal(Add(destRow[field.ColumnAlias], sourceRow[field.ColumnName])) / Convert.ToDecimal(rowCount)).ToString("0.000"));
                            //}
                            //else
                            //{
                                destRow[field.ColumnAlias] = Decimal.Parse((sumAll / Convert.ToDecimal(rowCount)).ToString("0.000"));
                                    //Decimal.Parse((Convert.ToDecimal(Add(destRow[field.ColumnAlias], sourceRow[field.ColumnName])) / Convert.ToDecimal(rowCount)).ToString("0.000"));
                            //}
                            break;
                        case "max":
                            destRow[field.ColumnAlias] = Max(destRow[field.ColumnAlias], sourceRow[field.ColumnName]);
                            break;
                        case "min":
                            if (rowCount == 1)
                            {
                                destRow[field.ColumnAlias] = sourceRow[field.ColumnName];
                            }
                            else
                            {
                                destRow[field.ColumnAlias] = Min(destRow[field.ColumnAlias], sourceRow[field.ColumnName]);
                            }
                            break;
                    }
                }
                lastSourceRow = sourceRow;
            }
            if (destRow != null)
            {
                destTable.Rows.Add(destRow);
            }
        }
        #endregion 插入记录到新的DataTable

        #region 私有方法：列设置和聚合列设置
        /// <summary>
        /// 根据条件，设置FieldInfo
        /// </summary>
        /// <param name="FieldString"></param>
        private void ParseGroupByFieldString(string FieldString)
        {
            if (GroupByFieldString == FieldString)
            {
                return;
            }
            FieldInfo fieldInfo;
            GroupByFieldInfoList = new ArrayList();
            string separator = @",";
            string[] Fields = Regex.Split(FieldString, separator, RegexOptions.IgnoreCase);
            string[] FieldParts;
            string[] FieldAggre;
            for (int i = 0, j = Fields.Length; i <= j - 1; i++)
            {
                fieldInfo = new FieldInfo();
                FieldParts = Fields[i].Trim().Split(' ');
                switch (FieldParts.Length)
                {
                    case 1: break;
                    case 2: fieldInfo.ColumnAlias = FieldParts[1]; break;
                    default: return;
                }

                FieldAggre = FieldParts[0].Split('(');
                switch (FieldAggre.Length)
                {
                    case 1: fieldInfo.ColumnName = FieldAggre[0]; break;
                    case 2: fieldInfo.ColumnAggre = FieldAggre[0].Trim().ToLower();
                        fieldInfo.ColumnName = FieldAggre[1].Trim(' ', ')');
                        break;
                    default: return;
                }
                if (fieldInfo.ColumnAlias == null)
                {
                    if (fieldInfo.ColumnAggre == null)
                    {
                        fieldInfo.ColumnAlias = fieldInfo.ColumnName;
                    }
                    else
                    {
                        fieldInfo.ColumnAlias = fieldInfo.ColumnAggre + "of" + fieldInfo.ColumnName;
                    }
                }
                GroupByFieldInfoList.Add(fieldInfo);
            }
            GroupByFieldString = FieldString;
        }

        /// <summary>
        /// 根据FieldInfo设置列属性
        /// </summary>
        /// <param name="FieldList"></param>
        /// <param name="SourceDt"></param>
        private void SetDataColumn(ArrayList FieldList, DataTable SourceDt)
        {
            foreach (FieldInfo field in FieldList)
            {
                string countString = field.ColumnAggre == null ? null : field.ColumnAggre.ToLower();
                if (countString == "count")
                {
                    DataColumn dc = SourceDt.Columns[field.ColumnName];
                    GroupDataColumn = new DataColumn();
                    GroupDataColumn.ColumnName = field.ColumnAlias;
                    GroupDataColumn.DataType = System.Type.GetType("System.Decimal");    //dc.DataType; //TODO
                    //GroupDataColumn.MaxLength = dc.MaxLength;
                    //GroupDataColumn.Expression = dc.Expression;
                    GroupDataTable.Columns.Add(GroupDataColumn);
                }
                else
                {
                    DataColumn dc = SourceDt.Columns[field.ColumnName];
                    GroupDataColumn = new DataColumn();
                    GroupDataColumn.ColumnName = field.ColumnAlias;
                    GroupDataColumn.DataType = dc.DataType;
                    GroupDataColumn.MaxLength = dc.MaxLength;
                    GroupDataColumn.Expression = dc.Expression;
                    GroupDataTable.Columns.Add(GroupDataColumn);
                }

            }
        }

        /// <summary>
        /// 判断列是否相等
        /// </summary>
        /// <param name="objectA"></param>
        /// <param name="objectB"></param>
        /// <returns></returns>
        private bool ColumnEqual(object objectA, object objectB)
        {
            if (objectA == DBNull.Value && objectB == DBNull.Value)
            {
                return true;
            }
            if (objectA == DBNull.Value || objectB == DBNull.Value)
            {
                return false;
            }
            return (objectA.Equals(objectB));
        }

        /// <summary>
        /// 判断行是否相等
        /// </summary>
        /// <param name="rowA"></param>
        /// <param name="rowB"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns)
        {
            bool result = true;
            for (int i = 0; i < columns.Count; i++)
            {
                result &= ColumnEqual(rowA[columns[i].ColumnName], rowB[columns[i].ColumnName]);
            }
            return result;
        }

        private void ParseFieldList(string fieldList, bool allowRelation)
        {
            //
            if (string.IsNullOrEmpty(fieldList))
            {
                return;
            }
            //
            if (m_FieldString == fieldList)
            {
                return;
            }
            m_FieldInfoList = new ArrayList();
            m_FieldString = fieldList;
            FieldInfo Field;
            string[] FieldParts;
            string[] Fields = fieldList.Split(',');
            for (int i = 0; i <= Fields.Length - 1; i++)
            {
                Field = new FieldInfo();
                FieldParts = Fields[i].Trim().Split(' ');
                switch (FieldParts.Length)
                {
                    case 1:
                        //to be set at the end of the loop 
                        break;
                    case 2:
                        Field.ColumnAlias = FieldParts[1];
                        break;
                    default:
                        return;
                }
                FieldParts = FieldParts[0].Split('.');
                switch (FieldParts.Length)
                {
                    case 1:
                        Field.ColumnName = FieldParts[0];
                        break;
                    case 2:
                        if (allowRelation == false)
                        {
                            return;
                        }
                        Field.RelationName = FieldParts[0].Trim();
                        Field.ColumnName = FieldParts[1].Trim();
                        break;
                    default:
                        return;
                }
                if (Field.ColumnAlias == null)
                {
                    Field.ColumnAlias = Field.ColumnName;
                }
                m_FieldInfoList.Add(Field);
            }
        }

        #endregion

        #region 聚合条件算法
        private object Min(object a, object b)
        {
            if ((a is DBNull) || (b is DBNull))
            {
                return DBNull.Value;
            }
            if (((IComparable)a).CompareTo(b) == -1)
            {
                return a;
            }
            else
            {
                return b;
            }
        }

        private object Max(object a, object b)
        {
            if (a is DBNull)
            {
                return b;
            }
            if (b is DBNull)
            {
                return a;
            }
            if (((IComparable)a).CompareTo(b) == 1)
            {
                return a;
            }
            else
            {
                return b;
            }
        }

        private object Add(object a, object b)
        {
            if (a is DBNull)
            {
                return (b is DBNull) ? 0 : b;
                //return b;
            }
            if (b is DBNull)
            {
                return (a is DBNull) ? 0 : a;
                //return a;
            }
            return ((decimal)a + (decimal)b);
        }
        #endregion

        #region Example 示例
        //支持：Sum/Avg/Count/Max/Min/First/Last
        //string destDtName = "TempTable";
        //string fieldString = @"spid,pihao,sum(shl) Total, avg(shl) AvgA, count(1) CC";
        //string filterString = @"spid <>''";
        //string groupString = @"spid,pihao";
        //DataTable newdt = new DataTable();
        //DataTableHelper dth = new DataTableHelper();
        //newdt = dth.SelectGroupDataTable(destDtName, dt, fieldString, filterString, groupString);
        #endregion

        #region 其他公用方法
        /// <summary>
        /// 根据Select条件返回与源DataTable结构相同的新DataTable
        /// expression 要注意，比如查询字段Type 为 MF的所有记录，则expression 应写为“Type = 'MF'”
        /// 注意等号两边的空格，以及字符串类型使用单引号
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="expression"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public DataTable SelectSimpleDataTable(DataTable dt, string expression, string sort)
        {
            DataTable newDt = new DataTable();
            newDt = dt.Clone();
            DataRow[] dr = dt.Select(expression, sort);
            for (int i = 0, j = dr.Length; i <= j - 1; i++)
            {
                newDt.ImportRow((DataRow)dr[i]);
            }
            return newDt;
        }

        //public DataTable GetDataTableColumnNameDT(DataTable dt, ArrayList ColNameList)
        //{
        //    DataTable ColDt = null;

        //    return ColDt;
        //}

        /// <summary>
        /// 获取DataTable的所有列名
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public ArrayList GetDataTableColumnNameList(DataTable dt)
        {
            ArrayList list = new ArrayList();
            for (int i = 0, j = dt.Columns.Count; i <= j - 1; i++)
            {
                list.Add(dt.Columns[i].ColumnName);
            }
            return list;
        }

        /// <summary>
        /// 获取DataTable指定类型的列名
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dataType">"System.Int32","System.String",,</param>
        /// <returns></returns>
        public ArrayList GetDataTableColumnNameList(DataTable dt, string dataType)
        {
            ArrayList list = new ArrayList();
            for (int i = 0, j = dt.Columns.Count; i <= j - 1; i++)
            {
                if (dt.Columns[i].DataType == Type.GetType(dataType))
                {
                    list.Add(dt.Columns[i].ColumnName);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取DataTable特定类型的列名
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dataType">类型：true：String；false：Other</param>
        /// <returns></returns>
        public ArrayList GetDataTableColumnNameList(DataTable dt, bool dataType)
        {
            ArrayList list = new ArrayList();
            if (dataType)
            {
                list = GetDataTableColumnNameList(dt, "System.String");
            }
            else
            {
                for (int i = 0, j = dt.Columns.Count; i <= j - 1; i++)
                {
                    if (dt.Columns[i].DataType != Type.GetType("System.String"))
                    {
                        list.Add(dt.Columns[i].ColumnName);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 获取DataSet里的DataTable
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public DataTable GetAllDataTable(DataSet ds)
        {
            DataTable newDt = ds.Tables[0].Clone();
            object[] objArray = new object[newDt.Columns.Count];
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                {
                    ds.Tables[i].Rows[j].ItemArray.CopyTo(objArray, 0);
                    newDt.Rows.Add(objArray);
                }
            }
            return newDt;
        }

        /// <summary>
        /// 获取DataSet里的DataTable的名称
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public ArrayList GetDataTableNameList(DataSet ds)
        {
            ArrayList list = new ArrayList();
            for (int i = 0, j = ds.Tables.Count; i <= j - 1; i++)
            {
                list.Add(ds.Tables[i].TableName);
            }
            return list;
        }

        /// <summary>
        /// 获取列的显示名称
        /// </summary>
        /// <param name="ValListTable">列名对应表</param>
        /// <param name="ColumnName">列名</param>
        /// <returns></returns>
        public string GetColumnShowName(string ValListTable, string ColumnName)
        {
            return null;
        }
        #endregion 其他公用方法

        public DataTable Changedtcolumns(DataTable _dt, DataTable _dt_columns)
        {
            int rowcount = _dt_columns.Rows.Count;
            int colcount = _dt.Columns.Count;
            for (int i = 0; i < colcount; i++)
            {
                for (int y = 0; y < rowcount; y++)
                {
                    if (_dt.Columns[i].ColumnName.ToString() == _dt_columns.Rows[y]["fdname"].ToString())
                    {
                        _dt.Columns[i].Caption = _dt_columns.Rows[y]["chnname"].ToString();
                        //_dt.Columns[i].ColumnName = _dt_columns.Rows[y]["chnname"].ToString();
                        break;
                    }
                }
            }
            return _dt;
        }
    }
}
